package com.neutech.studentgrade.dao.impl;

import com.neutech.studentgrade.dao.GradeDAO;
import com.neutech.studentgrade.dao.StudentDAO;
import com.neutech.studentgrade.entity.Grade;
import com.neutech.studentgrade.entity.Student;
import com.neutech.studentgrade.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImpl implements StudentDAO {
    @Override
    public List<Student> findAll() {
        Connection connection = null;
        Statement statement = null;
        String sql = "select id,sno,sname,sage,phone,classid from student";
        ResultSet resultSet = null;
        List<Student> students = new ArrayList<>();
        try {
            connection = JdbcUtil.getConnection();
            statement = JdbcUtil.getStatement(connection);
            resultSet = JdbcUtil.getResultSet(statement,sql);
            while (resultSet.next()){
               Student student = new Student();
               student.setId(resultSet.getInt("id"));
               student.setSno(resultSet.getNString("sno"));
               student.setSname(resultSet.getNString("sname"));
               student.setSage(resultSet.getInt("sage"));
               student.setPhone(resultSet.getString("phone"));
               student.setClassid(resultSet.getNString("classid"));
               students.add(student);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtil.close(statement,connection,resultSet);
        }
        return students;
    }

    @Override
    public List<Student> findAllRankBySno() {
        Connection connection = null;
        Statement statement = null;
        String sql = "select t.sno sno,t.sname sname,t.sum sum,t.avg avg,@curRank := @curRank + 1 as rank\n" +
                "from (SELECT  sc.sno sno,student.sname sname,sum(grade) sum,round(avg(grade),2) avg FROM `sc` JOIN student on sc.sno = student.sno GROUP BY sc.sno ORDER BY sum(grade) desc\n" +
                ") as t,(select @curRank := 0) q";
        ResultSet resultSet = null;
        List<Student> students = new ArrayList<>();
        try {
            connection = JdbcUtil.getConnection();
            statement = JdbcUtil.getStatement(connection);
            resultSet = JdbcUtil.getResultSet(statement,sql);
            while (resultSet.next()){
                Student student = new Student();
                student.setSno(resultSet.getNString("sno"));
                student.setSname(resultSet.getNString("sname"));
                student.setSum(resultSet.getDouble("sum"));
                student.setAvg(resultSet.getDouble("avg"));
                student.setRank(resultSet.getInt("rank"));
                students.add(student);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtil.close(statement,connection,resultSet);
        }
        return students;
    }

    @Override
    public List<Student> findAllAvgBySno() {
        Connection connection = null;
        Statement statement = null;
        String sql = " select sc2.grade avg,sc1.sno sno,student.sname sname,sc1.cno cno,course.cname cname,sc1.grade grade " +
                "from sc sc1,(select  cno,ROUND(avg(grade),2) grade from sc GROUP BY sc.cno) sc2,course,student where " +
                "sc1.cno = sc2.cno and sc1.cno = course.cno and student.sno=sc1.sno and sc1.grade < sc2.grade ORDER BY sc1.cno\n";
        ResultSet resultSet = null;
        List<Student> students = new ArrayList<>();
        try {
            connection = JdbcUtil.getConnection();
            statement = JdbcUtil.getStatement(connection);
            resultSet = JdbcUtil.getResultSet(statement,sql);
            while (resultSet.next()){
                Student student = new Student();
                student.setAvg(resultSet.getDouble("avg"));
                student.setSno(resultSet.getNString("sno"));
                student.setSname(resultSet.getNString("sname"));
                student.setCno(resultSet.getNString("cno"));
                student.setCname(resultSet.getNString("cname"));
                student.setGrade(resultSet.getDouble("grade"));
                students.add(student);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtil.close(statement,connection,resultSet);
        }
        return students;
    }

    @Override
    public void insert(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "insert into student(sno,sname,sage,phone,classid) values(?,?,?,?,?)";
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = JdbcUtil.getPreparedStatement(connection,sql);
            preparedStatement.setString(1,student.getSno());
            preparedStatement.setString(2,student.getSname());
            preparedStatement.setInt(3,student.getSage());
            preparedStatement.setString(4,student.getPhone());
            preparedStatement.setString(5,student.getClassid());

            preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollback(connection);
        }finally {
            JdbcUtil.close(preparedStatement,connection);
        }
    }

    @Override
    public void delete(String sno) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "delete from student where sno = ?";
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = JdbcUtil.getPreparedStatement(connection,sql);
            preparedStatement.setString(1,sno);
            preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public Student selectBySno(String sno) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Student student = null;
//        String sql = "select id,user_name,user_password,user_phone,user_addr,user_createTime,user_updateTime,user_status from" +
//                "t_user where id = ?";
        String sql = "select id,sno,sname,sage,phone,classid from student where sno = ?" ;
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = JdbcUtil.getPreparedStatement(connection,sql);
            preparedStatement.setString(1,sno);
            resultSet = JdbcUtil.getResultSet(preparedStatement);
            if (resultSet.next()){
                student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setSno(resultSet.getNString("sno"));
                student.setSname(resultSet.getNString("sname"));
                student.setSage(resultSet.getInt("sage"));
                student.setPhone(resultSet.getString("phone"));
                student.setClassid(resultSet.getNString("classid"));

            }
        }catch (Exception e){
            e.printStackTrace();
            //出现异常，事务不提交，回滚
            JdbcUtil.rollback(connection);
        }finally {
            JdbcUtil.close(resultSet,preparedStatement,connection);
        }
        return student;
    }

    @Override
    public void update(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "update student set sname = ?,sage = ?,phone = ?,classid = ? where sno = ?";
        try{
            connection = JdbcUtil.getConnection();
            preparedStatement = JdbcUtil.getPreparedStatement(connection,sql);
            preparedStatement.setString(1,student.getSname());
            preparedStatement.setInt(2,student.getSage());
            preparedStatement.setString(3,student.getPhone());
            preparedStatement.setString(4,student.getClassid());
            preparedStatement.setString(5,student.getSno());
            preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
//        StudentDAO dao = new StudentDAOImpl();
//        List<Student> students = dao.findAll();
//        students.forEach(student -> {
//            System.out.println(student);
//        });
        GradeDAO dao = new GradeDAOImpl();
        List<Grade> grades = dao.findGradeBySno("S001");
        grades.forEach(grade -> {
            System.out.println(grade);
        });
 //       Grade grade = dao.findGradeBySno("S001");
//        System.out.println(grade);
    }
}


























